package cs.sy.service.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import cs.sy.common.conn.DBConnection;
import cs.sy.service.bean.AllDataBean;
import cs.sy.service.bean.FoodBean;
import cs.sy.service.bean.UserBean;

/**
 * 作者： sunhouji
 * 时间： 2011-8-4 下午12:54:46
 */
public class UserLoginDao {
	
	private final Log log = LogFactory.getLog(UserLoginDao.class);

	public UserBean getUserByNamePass(String name,String pass){
		String sql = "select * from user_info where name = '"+name + "' and pass='"+pass+"'";
		ResultSet rs=null;
		PreparedStatement ps=null;
		Connection conn=null;
		DBConnection db=new DBConnection();
		conn=db.getCon();
		try{
			ps = conn.prepareStatement(sql);
			rs= ps.executeQuery();
			UserBean ub = new UserBean();
			while(rs.next()){
				int id = rs.getInt("u_id");
				String t_name = rs.getString("name");
				int sex = rs.getInt("sex");
				Date u_time = rs.getTimestamp("u_time");
				System.err.print(u_time);
				String p = rs.getString("pass");
				ub.setPass(p);
				ub.setId(id);
				ub.setName(t_name);
				ub.setSex(sex);
				ub.setU_time(u_time);
				return ub; 
			}
		}catch (Exception e) {
			log.error("...",e);
			e.printStackTrace();
		}
		
		return null;
	}
	
	/**
	 * 插入用户信息
	 * @param userBean
	 */
	public boolean insertUserBean(UserBean userBean) throws Exception{
		String sql = "insert into user_info(name,pass,sex,u_time) values('"
			+userBean.getName()+"','"+userBean.getPass()+"',"+userBean.getSex()+",now())";
		PreparedStatement ps=null;
		Connection conn=null;
		DBConnection db=new DBConnection();
		conn=db.getCon();
		try{
			ps = conn.prepareStatement(sql);
			int result = ps.executeUpdate();
			if(result == 1)
			return true;
		}catch (Exception e) {
			log.error("...",e);
			e.printStackTrace();
			throw new Exception(e);
		}
		return false;
	}
	
	public List<FoodBean> getAllFood(){
		String sql = "select * from food_info where shop_name = '一招鲜'";
		List<FoodBean> foodBeans = new ArrayList<FoodBean>();
		ResultSet rs=null;
		PreparedStatement ps=null;
		Connection conn=null;
		DBConnection db=new DBConnection();
		conn=db.getCon();
		try{
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()){
				FoodBean foodBean = new FoodBean();
				int u_id = rs.getInt("u_id");
				String food_name = rs.getString("food_name");
				int food_price = rs.getInt("food_price");
				String shop_name = rs.getString("shop_name");
				foodBean.setU_id(u_id);
				foodBean.setFood_name(food_name);
				foodBean.setFood_price(food_price);
				foodBean.setShop_name(shop_name);
				foodBeans.add(foodBean);
			}
		}catch (Exception e) {
			log.error("...",e);
			e.printStackTrace();
		}
		return foodBeans;
	}
	
	public boolean insertOrderDetail(String u_id,String c_id) throws Exception{
		String sql = "insert into user_order_detail(u_id,c_id,u_time) values("+u_id+","+c_id+",now())";
		PreparedStatement ps=null;
		Connection conn=null;
		DBConnection db=new DBConnection();
		conn=db.getCon();
		try{
			ps = conn.prepareStatement(sql);
			int result = ps.executeUpdate();
			if(result == 1)
			return true;
		}catch (Exception e) {
			log.error("...",e);
			e.printStackTrace();
			throw new Exception(e);
		}
		return false;
	}
	
	public List<AllDataBean> getAllData(){
		String sql = "select u2.name,f.food_name,f.food_price,u1.u_time, u1.id,u1.s_status,f.shop_name from user_order_detail u1,user_info u2,food_info f where u1.u_id = u2.u_id and u1.c_id = f.u_id";
		List<AllDataBean> allDataBeans = new ArrayList<AllDataBean>();
		ResultSet rs=null;
		PreparedStatement ps=null;
		Connection conn=null;
		DBConnection db=new DBConnection();
		conn=db.getCon();
		try{
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()){
				AllDataBean allDataBean = new AllDataBean();
				String u_name =rs.getString(1);
				String food_name = rs.getString(2);
				String food_price = rs.getString(3);
				Date u_time = rs.getTimestamp(4);
				int id = rs.getInt(5);
				int s_status = rs.getInt(6);
				String shop_name = rs.getString(7);
				allDataBean.setShop_name(shop_name);
				allDataBean.setS_status(s_status);
				allDataBean.setId(id);
				allDataBean.setU_name(u_name);
				allDataBean.setFood_name(food_name);
				allDataBean.setFood_price(food_price);
				allDataBean.setU_time(u_time);
				allDataBeans.add(allDataBean);
			}
		}catch (Exception e) {
			log.error("...",e);
			e.printStackTrace();
		}
		return allDataBeans;
	}
	
	public UserBean getUserByName(String name){
		String sql = "select * from user_info where name = '"+name + "'";
		ResultSet rs=null;
		PreparedStatement ps=null;
		Connection conn=null;
		DBConnection db=new DBConnection();
		conn=db.getCon();
		try{
			ps = conn.prepareStatement(sql);
			rs= ps.executeQuery();
			UserBean ub = new UserBean();
			while(rs.next()){
				int id = rs.getInt("u_id");
				String t_name = rs.getString("name");
				int sex = rs.getInt("sex");
				Date u_time = rs.getTimestamp("u_time");
				ub.setId(id);
				ub.setName(t_name);
				ub.setSex(sex);
				ub.setU_time(u_time);
				return ub; 
			}
		}catch (Exception e) {
			log.error("...",e);
			e.printStackTrace();
		}
		
		return null;
	}
	
	public List<AllDataBean> getAllDataByDate(){
		String sql = "select u2.name,f.food_name,f.food_price,u1.u_time, u1.id,u1.s_status ,f.shop_name from user_order_detail u1,user_info u2,food_info f where DATE(NOW()) = DATE(u1.u_time) and u1.u_id = u2.u_id and u1.c_id = f.u_id order by f.food_name";
		List<AllDataBean> allDataBeans = new ArrayList<AllDataBean>();
		ResultSet rs=null;
		PreparedStatement ps=null;
		Connection conn=null;
		DBConnection db=new DBConnection();
		conn=db.getCon();
		try{
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()){
				AllDataBean allDataBean = new AllDataBean();
				String u_name =rs.getString(1);
				String food_name = rs.getString(2);
				String food_price = rs.getString(3);
				Date u_time = rs.getTimestamp(4);
				int id = rs.getInt(5);
				int s_status = rs.getInt(6);
				String shop_name = rs.getString(7);
				allDataBean.setShop_name(shop_name);
				allDataBean.setS_status(s_status);
				allDataBean.setId(id);
				allDataBean.setU_name(u_name);
				allDataBean.setFood_name(food_name);
				allDataBean.setFood_price(food_price);
				allDataBean.setU_time(u_time);
				allDataBeans.add(allDataBean);
			}
		}catch (Exception e) {
			log.error("...",e);
			e.printStackTrace();
		}
		return allDataBeans;
	}
	
	
	public AllDataBean getDataByDay(String u_id){
		String sql = "select u2.name,f.food_name,f.food_price,u1.u_time, u1.id from user_order_detail u1,user_info u2,food_info f where u1.u_id="+u_id +" and DATE(NOW()) = DATE(u1.u_time) and u1.u_id = u2.u_id and u1.c_id = f.u_id";
		ResultSet rs=null;
		PreparedStatement ps=null;
		Connection conn=null;
		DBConnection db=new DBConnection();
		conn=db.getCon();
		try{
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()){
				AllDataBean allDataBean = new AllDataBean();
				String u_name =rs.getString(1);
				String food_name = rs.getString(2);
				String food_price = rs.getString(3);
				Date u_time = rs.getTimestamp(4);
				int id = rs.getInt(5);
				allDataBean.setId(id);
				allDataBean.setU_name(u_name);
				allDataBean.setFood_name(food_name);
				allDataBean.setFood_price(food_price);
				allDataBean.setU_time(u_time);
				return allDataBean;
			}
		}catch (Exception e) {
			log.error("...",e);
			e.printStackTrace();
		}
		return null;
	}
	
	public boolean delOrderById(int id) throws Exception{
		String sql = "delete from user_order_detail where id = "+id;
		PreparedStatement ps=null;
		Connection conn=null;
		DBConnection db=new DBConnection();
		conn=db.getCon();
		try{
			ps = conn.prepareStatement(sql);
			int result = ps.executeUpdate();
			if(result == 1)
			return true;
		}catch (Exception e) {
			log.error("...",e);
			e.printStackTrace();
			throw new Exception(e);
		}
		return false;
	}
	
	public boolean repairPass(String pass,int id) throws Exception{
		String sql = "update user_info set pass = '"+pass+"' where u_id = "+id;
		PreparedStatement ps=null;
		Connection conn=null;
		DBConnection db=new DBConnection();
		conn=db.getCon();
		try{
			ps = conn.prepareStatement(sql);
			int result = ps.executeUpdate();
			if(result == 1)
			return true;
		}catch (Exception e) {
			log.error("...",e);
			e.printStackTrace();
			throw new Exception(e);
		}
		return false;
	}
	
	public boolean pay(int id) throws Exception{
		String sql = "update user_order_detail set s_status = 1 where id = "+id;
		PreparedStatement ps=null;
		Connection conn=null;
		DBConnection db=new DBConnection();
		conn=db.getCon();
		try{
			ps = conn.prepareStatement(sql);
			int result = ps.executeUpdate();
			if(result == 1)
			return true;
		}catch (Exception e) {
			log.error("...",e);
			e.printStackTrace();
			throw new Exception(e);
		}
		return false;
	}
	
	public List<FoodBean> getAllFoodByShopName(String shopname){
		String sql = "select * from food_info where shop_name = '"+shopname+"'";
		List<FoodBean> foodBeans = new ArrayList<FoodBean>();
		ResultSet rs=null;
		PreparedStatement ps=null;
		Connection conn=null;
		DBConnection db=new DBConnection();
		conn=db.getCon();
		try{
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()){
				FoodBean foodBean = new FoodBean();
				int u_id = rs.getInt("u_id");
				String food_name = rs.getString("food_name");
				int food_price = rs.getInt("food_price");
				String shop_name = rs.getString("shop_name");
				foodBean.setU_id(u_id);
				foodBean.setFood_name(food_name);
				foodBean.setFood_price(food_price);
				foodBean.setShop_name(shop_name);
				foodBeans.add(foodBean);
			}
		}catch (Exception e) {
			log.error("...",e);
			e.printStackTrace();
		}
		return foodBeans;
	}
}

